The AND & OR Clauses
In this lesson, we will discuss how can we combine multiple conditions in WHERE using the AND & OR operators.
We'll cover the following
The AND & OR clauses#
The SQL AND & OR operators are used to combine multiple conditions in order to narrow data in an SQL statement. These two operators are called the conjunctive operators.
These operators provide a means to make multiple comparisons with different operators in the same SQL statement.
The AND Operator#
The AND operator allows the existence of multiple conditions in a SQL statement’s WHERE clause.
Syntax#
The basic syntax of the AND operator with a WHERE clause is as follows:
SELECT column1, column2, ... columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];
You can combine N number of conditions using the AND operator. For an action to be taken by the SQL statement, whether it be a transaction or a query, all conditions separated by the AND must be TRUE.
Example#
In this example, we will retrieve the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than $20,000 (inclusive) and the age is less than 25 years.
The steps needed to solve this problem are highlighted below:
The following code shows how to do this in SQL:
The OR Operator#
The OR operator is used to combine multiple conditions in a SQL statement’s WHERE clause.
Syntax#
The basic syntax of the OR operator with a WHERE clause is as follows:
SELECT column1, column2, ... columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN];
You can combine N number of conditions using the OR operator. For an action to be taken by the SQL statement, whether it be a transaction or query, any ONE of the conditions separated by the OR can be TRUE.
Example#
Consider the following query, which will fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than $50,000 or the age is less than 25 years.
The following slides show the steps needed to solve this problem:
The following code shows how to do this in SQL:
Quick quiz!#
Which of the following query will return the customers who have SALARY between 40000 and 60000?
A)
SELECT *
FROM CUSTOMERS
WHERE SALARY > 40000 OR SALARY < 60000;
B)
SELECT *
FROM CUSTOMERS
WHERE SALARY > 40000 AND SALARY < 60000;
C)
SELECT *
FROM CUSTOMERS
WHERE SALARY > 40000 OR SALARY < 60000
D)
SELECT *
FROM CUSTOMERS
WHERE SALARY > 40000 AND SALARY < 60000
In the next lesson, we will learn about SQL aggregate functions.
